/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package org.dusin.teachassistdbservice.dao4mysql;

import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.dusin.teachassistdbservice.dao.JobDao;
import org.dusin.teachassistdbservice.util.DateUtil;

import org.springframework.jdbc.core.JdbcTemplate;

/**
 *
 * @author dusin
 */
public class JobDaoImpl implements JobDao{
    static Logger logger = Logger.getLogger(JobDaoImpl.class);
    private final JdbcTemplate jdbcTemplate;
    
    public JobDaoImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    
    @Override
    public boolean doCreate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "insert into job(teachclassid,no,title,duedate,threshold,type,note) values(?,?,?,?,?,?,?)";
        logger.debug(sql+"?"+bean);
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    /*是否存在成绩记录*/
    @Override
    public boolean isScoreExisted(int jobId,String studentNo){
        String sql = null;
        sql = "select count(*) from score where jobId= ? and studentNo=?";
        int c =  jdbcTemplate.queryForObject(sql,Integer.class,jobId,studentNo);
        return c>0?true:false;
    }
    
    @Override
    public boolean doSubmitReport(Object[] bean){
        boolean flag = true;
        String sql = null;
        sql = "insert into score(jobId,studentno,score,time,count,status,path,note,degree,degreeMatchFile) values(?,?,?,?,?,?,?,?,?,?)";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    
    // 重新提交报告 status--0 未提交；status-1 已提交; status-3 已评分，不可修改;
    //提交后 score更新为0
    @Override 
    public boolean doUpdateReport(int jobId,String studentNo,int score,
            int status,String note,String filename,double degree,String matchFilename){
        boolean flag = true;
        String time = DateUtil.get(new Date());
        String sql = null;
        sql = "update score set score=?, status=?,note=?,time=?,count=count+1 ,path=?,degree=?,degreeMatchFile=? where jobId=? and studentno=? and status<>3";
        int rows = jdbcTemplate.update(sql,score,status,note,time,filename,degree,matchFilename, jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    // 取消报告提交，删除文件并更改状态  status=0,表示报告未提交，score =-1,表示为提交状态
    @Override 
    public boolean doCancelReport(int jobId,String studentNo){
        boolean flag = true;
        String sql = null;
        sql = "update score set score=-1,status=0 ,time=null where jobId=? and studentno=? and status<>3";
        int rows = jdbcTemplate.update(sql, jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    //status==3 已评分，学生不可再提交修改
    @Override
    public boolean doSetScore(int jobId,String studentNo,int score,String note){
        boolean flag = true;
        String sql = null;
        sql = "update score set status=3 ,score=?,note=? where jobId=? and studentno=? ";
        int rows = jdbcTemplate.update(sql, score,note,jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    //修改状态 status==3 
    @Override
    public boolean doSetStatus(int jobId,String studentNo,int status){
        boolean flag = true;
        String sql = null;
        sql = "update score set status=?  where jobId=? and studentno=? ";
        int rows = jdbcTemplate.update(sql, status,jobId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doDeleteByID(int id) {
        boolean flag = true;
        String sql = null;
        sql = "delete from job where id = ?";
        logger.debug(sql);
        int rows = jdbcTemplate.update(sql, id);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    @Override
    public boolean doUpdate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "update job set no=?,title=?,duedate=?,threshold=?,note=? where id = ?";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doChangeStatus(int id,int status) {
        boolean flag = true;
        String sql = null;
        sql = "update job set status=? where id = ?";
        int rows = jdbcTemplate.update(sql, status,id);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    @Override
    public Map<String, Object> findByNo(String no) {
        String sql = null;
        sql = "select * from teachclass where teachclassno=?";
        return jdbcTemplate.queryForMap(sql,no);
    }

    @Override
    public List<Map<String, Object>> findAll() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAll(int start, int size) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAllByTeachClassId(int teachClassId,int type) {
        String sql = null;
        sql = "select * from vjob where teachclassid=? and type=? order by no";
        return jdbcTemplate.queryForList(sql,teachClassId,type);
    }
    
    @Override
    public List<Map<String, Object>> findAllByTeachClassId(int teachClassId){
        String sql = null;
        sql = "select * from job where teachclassid=? order by no";
        return jdbcTemplate.queryForList(sql,teachClassId);
    }
    
    /**
     * 
     * @param teachClassId
     * @param studentNo
     * @return 
     */
    @Override
    public List<Map<String,Object>> findAllCourseJobs4Student(int teachClassId,String studentNo){
        String sql = null;
        sql = "select t1.id as id, no,title,duedate,jstatus,jnote,score,time,count,sstatus ,path,snote ,threshold from  "
                +"(select job.id as id, no,title,duedate,job.status as jstatus,job.note as jnote,job.threshold as threshold from job where teachclassid=? )  t1 left join "
                +" (select jobid, score,time,count,score.status as sstatus,path,score.note as snote from score where  studentNo =?) t2 "
                + " on id = jobid order by no";
        return jdbcTemplate.queryForList(sql,teachClassId,studentNo);
    }

    @Override
    public List<Map<String, Object>> findHomeworkDetail(int teachClassId, int jobId) {
        String sql = null;
        sql = "select no,t1.studentno,name,ifnull(score,0) score,ifnull(status,0) status,time,count,path,anote note,degree,degreeMatchFile from (select no,studentclass.studentno,name from studentclass left join student on studentclass.studentno=student.studentno where classID = ? order by studentno) t1 left join (select studentno,score,status,time,count,path,note  as anote,degree,degreeMatchFile from score where jobid=?) t2 on t1.studentno = t2.studentno";
        return jdbcTemplate.queryForList(sql,teachClassId,jobId);
    }

    @Override
    public int[] doUpdateScoreBatch(List<Object[]> beans) {
         boolean flag = true;
        String sql = null;
        sql = "update score set score=?,status=3,note=? where jobId=?   and studentNo=?";
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        return rows;
    }

    @Override
    public boolean doCreateDetailBatch(List<Object[]> beans) {
         boolean flag = true;
        String sql = null;
        sql = "insert into score(jobId,studentno,score,time,count,status,path,note) values(?,?,?,?,?,?,?,?)";
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        return true;
    }

    @Override
    public List<Map<String, Object>> findAllJobDetail(int teachClassId) {
        String sql = "select * from score where jobId in (select id from job where teachclassid = ?)";
        return jdbcTemplate.queryForList(sql,teachClassId);
    }
    
   
}
